package com.yun2.bms.dao.impl;

import com.yun2.bms.dao.ICategoryDao;
import com.yun2.bms.model.Category;
import com.yun2.bms.model.User;
import com.yun2.bms.utils.DBUtils;
import org.apache.commons.lang3.StringUtils;

import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * author: youli
 * qq: 27855162@qq.com
 * date: 2020/5/25
 */

public class CategoryDaoImpl extends BaseDaoImpl implements ICategoryDao {
    @Override
    public List<Category> getAll() {
        List<Category> list = new ArrayList<Category>();
        try {
            String sql = "select * from category";
            Object[] params = {};
            super.queryBySql(sql, params);
            while (rs.next()) {
                Category category = new Category();
                makeModel(category);
                list.add(category);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(conn, st, rs);
        }
        return list;
    }

    @Override
    public int add(Category category) {
        String sql = "insert into category(category_name)values(?)";
        Object[] params = {category.getCategoryName()};
        return super.insertBySql(sql, params);
    }

    @Override
    public boolean isCategoryNameUnique(String categoryName) {
        int count = 0;
        String sql = "select count(*) from category where category_name = ?";
        Object[] params = {categoryName};
        try {
            super.queryBySql(sql, params);
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(conn, st, rs);
        }
        return count > 0;
    }

    @Override
    public boolean isCategoryNameUnique(Integer id, String categoryName) {
        int count = 0;
        String sql = "select count(*) from category where category_name = ? and id <> ?";
        Object[] params = {categoryName,id};
        try {
            super.queryBySql(sql, params);
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(conn, st, rs);
        }
        return count > 0;
    }

    @Override
    public int edit(Category category) {
        String sql = "update category c set c.category_name = ? where c.id = ?";
        Object[] params = {category.getCategoryName(), category.getId()};
        return super.updateBySql(sql, params);
    }

    @Override
    public int delete(String ids) {
        String sql = "delete from category where id in (?)";
        Object[] params = {ids};
        return super.updateBySql(sql, params);
    }

    @Override
    public List<Category> getAllByCategoryName(String categoryName) {
        List<Category> list = new ArrayList<Category>();
        try {
            String sql = "select * from category where category_name like ?";
            Object[] params = {"%"+StringUtils.trim(categoryName)+"%"};
            super.queryBySql(sql, params);
            while (rs.next()) {
                Category category = new Category();
                makeModel(category);
                list.add(category);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(conn, st, rs);
        }
        return list;
    }

    @Override
    public Category get(int id) {
        Category category = null;
        try {
            String sql = "select * from category where id=?";
            Object[] params = {id};
            //将查询到的数据放在rs中
            super.queryBySql(sql, params);
            while (rs.next()) {
                category = new Category();
                makeModel(category);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(conn, st, rs);
        }
        return category;
    }

    private void makeModel(Category category) throws SQLException {
        category.setId(rs.getInt("id"));
        category.setCategoryName(rs.getString("category_name"));
    }
}
